package com.springboot.project.util;

import com.springboot.project.common.ExcelResultEnum;
import com.springboot.project.entity.ExcelResult;
import com.springboot.project.entity.ExcelTemplate;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.util.StringUtils;

import java.util.ArrayList;
import java.util.List;

public class ExcelUtil {

    private static final Log logger = LogFactory.getLog(ExcelUtil.class);

    /**
     * 根据文件后缀 是否是XLS 判断文件格式
     *
     * @param filePath
     * @return
     */
    public static boolean isExcel2003(String filePath) {
        return !StringUtils.isEmpty(filePath) && filePath.matches("^.+\\.(?i)(xls)$");
    }

    /**
     * 根据文件后缀 是否是XLSX 判断文件格式
     *
     * @param filePath
     * @return
     */
    public static boolean isExcel2007(String filePath) {
        return !StringUtils.isEmpty(filePath) && filePath.matches("^.+\\.(?i)(xlsx)$");
    }

    /**
     * 检查文件名是否为空或者是否是Excel格式的文件
     */
    public static boolean validateExcel(String filePath) {
        return ExcelUtil.isExcel2003(filePath) || ExcelUtil.isExcel2007(filePath);
    }

    /**
     * 根据Workbook 和 Excel模版获取文件内容
     *
     * @param wb
     * @param template
     * @return
     */
    public static ExcelResult parseExcelByTemplate(Workbook wb, ExcelTemplate template) {
        StringHandle.letterToNumber(template.getEndCol());
        Sheet sheet = getSheet(wb, template.getReportDateSheetName());
        if (sheet != null) {
            List<List<String>> lists = getGivenData(sheet, template);
            return new ExcelResult(lists);
        } else {
            logger.info("没有找到工作表：" + template.getReportDateSheetName());
            return new ExcelResult(ExcelResultEnum.EXCEL_NOTFOUND_SHEET);
        }


    }


    /**
     * 根据Workbook 和 sheet名字获取 sheet工作表
     *
     * @param wb
     * @param sheetName
     * @return
     */
    public static Sheet getSheet(Workbook wb, String sheetName) {
        Sheet sheet = null;
        for (int i = 0; i < wb.getNumberOfSheets(); i++) {
            Sheet temp = wb.getSheetAt(i);
            if (temp.getSheetName().equals(sheetName)) {
                sheet = temp;
            }
        }
        return sheet;
    }

    private static List<List<String>> getGivenData(Sheet sheet, ExcelTemplate template) {
        Integer startCol = StringHandle.letterToNumber(template.getStartCol());
        Integer endCol = StringHandle.letterToNumber(template.getEndCol());
        Integer startRow = template.getStartRow();
        Integer endRow = template.getEndRow();
        List<List<String>> lists = new ArrayList<>();
        if (template.getFixedRow() == 1) {
            for (int j = startCol - 1; j < endCol; j++) {
                List<String> list = new ArrayList<>();
                for (int i = startRow - 1; i < endRow; i++) {
                    Row row = sheet.getRow(i);
                    list.add(row.getCell(j).toString());
                }
                lists.add(list);
            }
        } else if (template.getFixedRow() == 0) {
            for (int i = startRow - 1; i < endRow; i++) {
                Row row = sheet.getRow(i);
                List<String> list = new ArrayList<>();
                for (int j = startCol - 1; j < endCol; j++) {
                    list.add(row.getCell(j).toString());
                }
                lists.add(list);
            }
            sheet.getRow(startRow).getCell(startCol);

        }
        return lists;
    }
}
